package ca.ualberta.cs.CLRDB.statistics;

import java.sql.SQLException;
import java.sql.Statement;

import ca.ualberta.cs.CLRDB.EntityRelManagement;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

public class UserStatistics extends EntityRelManagement{

	public UserStatistics(){
		mainTableName = "UserStat";
	}
	
	@Override
	public void initialize(Statement statement) throws SQLException {
/*		try{
			statement.executeUpdate("drop table UserStat");			
		}
		catch(Exception e){}		
		
		statement.executeUpdate("CREATE TABLE UserStat (" +
				"personId INTEGER NOT NULL, " +
				"readings INTEGER WITH DEFAULT 0, " +
				"comments INTEGER WITH DEFAULT 0, " +
				"resourceratings INTEGER WITH DEFAULT 0, " +
				"tags INTEGER WITH DEFAULT 0, " +
				"publications INTEGER WITH DEFAULT 0, " +
				"friends INTEGER WITH DEFAULT 0, " + //number of friends
				"friendsscore INTEGER WITH DEFAULT 0, " + //based on active index of friends
				"startdiscussions INTEGER WITH DEFAULT 0, " +
				"continuediscussions INTEGER WITH DEFAULT 0, " +				
				"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
					"REFERENCES Person (ID) ON DELETE CASCADE, " +
				"CONSTRAINT PK_person PRIMARY KEY (personId) " +
			") IN CLRSPACE ");
		
		//add triggers:
		*/
		userTrigger(statement);
		/*
		readingsTrigger(statement);
		commentsTrigger(statement);
		resourceratingsTrigger(statement);
		tagsTrigger(statement);
		publicationsTrigger(statement);		
		friendsTrigger(statement);
		//friendsscore...
		startdiscussionsTrigger(statement);
		continuediscussionsTrigger(statement);*/
		
	}
	
	private void userTrigger(Statement statement){
		String triggername = "userTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Person " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 					
					"INSERT INTO UserStat (personId) VALUES (n.ID) ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	private void readingsTrigger(Statement statement){
		String triggername = "userReadingsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Reading " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 					
					"UPDATE UserStat u SET readings = (readings + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	private void commentsTrigger(Statement statement){
		String triggername = "userCommentsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Annotation " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"UPDATE UserStat u SET u.comments = (u.comments + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	private void resourceratingsTrigger(Statement statement){
		String triggername = "userResourceRatingsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON RateResource " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"UPDATE UserStat u SET u.resourceratings = (u.resourceratings + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	private void tagsTrigger(Statement statement){
		String triggername = "userTagsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Tagging " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"UPDATE UserStat u SET u.tags = (u.tags + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	private void publicationsTrigger(Statement statement){
		String triggername = "userPublicationsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Authoring " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"BEGIN ATOMIC " + 
					"DECLARE x INT; " +
					"SET (x) = (SELECT COUNT(*) FROM Person p WHERE p.username IS NOT NULL AND p.ID = n.personId); " +
					"IF x != 0 THEN " +
					"UPDATE UserStat u SET u.publications = (u.publications + 1) WHERE u.personId = n.personId; " +
					"END IF; " +
					"END");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}

	private void friendsTrigger(Statement statement){
		String triggername = "userFriendsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Friend " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"UPDATE UserStat u SET u.friends = (u.friends + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
/*	private void friendsscoreTrigger(Statement statement){
		String triggername = "userFriendsscoreTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER UPDATE?? ON ?? " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"UPDATE UserStat u SET u.friends = (u.friends + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
*/

	private void startdiscussionsTrigger(Statement statement){
		String triggername = "userStartdiscussionsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER UPDATE OF hasReply ON Annotation " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( lower(n.hasReply) = 'y' AND n.replyTo IS NULL ) "+
					"UPDATE UserStat u SET u.startdiscussions = (u.startdiscussions + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}
	
	private void continuediscussionsTrigger(Statement statement){
		String triggername = "userContinuediscussionsTrigger";
		try{
			statement.executeUpdate("drop trigger "+triggername);
		}
		catch(Exception e){}
		
		try{
			statement.executeUpdate("CREATE TRIGGER "+triggername+" AFTER INSERT ON Annotation " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( n.replyTo IS NOT NULL ) "+
					"UPDATE UserStat u SET u.continuediscussions = (u.continuediscussions + 1) WHERE u.personId = n.personId ");
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
	}


}
